using Dapper;
using MediatR;
using RBAC.ErrorCode;
using RBAC.Read.Api.Application.Command.ERP.SalesManagement.H;
using RBAC.Interstructrue.Base;
using static RBAC.ErrorCode.APIEnums;

namespace RBAC.Read.Api.Application.Handler.ERP.SalesManagement.H
{
    /// <summary>
    /// 销售订单计划查询处理器（与截图列一致）
    /// </summary>
    public class GetSalesOrderPlanListHandler : IRequestHandler<GetSalesOrderPlanListCommand, APIResult<GetSalesOrderPlanListDto>>
    {
        private readonly IDbConnectionFactory _dbConnectionFactory;

        public GetSalesOrderPlanListHandler(IDbConnectionFactory dbConnectionFactory)
        {
            _dbConnectionFactory = dbConnectionFactory;
        }

        public async Task<APIResult<GetSalesOrderPlanListDto>> Handle(GetSalesOrderPlanListCommand request, CancellationToken cancellationToken)
        {
            try
            {
                using var connection = _dbConnectionFactory.CreateConnection();

                var where = new List<string>();
                var p = new DynamicParameters();

                if (!string.IsNullOrWhiteSpace(request.SalesPerson))
                {
                    where.Add("so.SalesPerson LIKE @SalesPerson");
                    p.Add("@SalesPerson", $"%{request.SalesPerson.Trim()}%");
                }
                if (!string.IsNullOrWhiteSpace(request.SalesOrderNumber))
                {
                    where.Add("so.OrderCode LIKE @SalesOrderNumber");
                    p.Add("@SalesOrderNumber", $"%{request.SalesOrderNumber.Trim()}%");
                }
                if (!string.IsNullOrWhiteSpace(request.BusinessType))
                {
                    where.Add("so.BusinessType LIKE @BusinessType");
                    p.Add("@BusinessType", $"%{request.BusinessType.Trim()}%");
                }
                if (!string.IsNullOrWhiteSpace(request.DeliveryStatus))
                {
                    where.Add("so.DeliveryStatus = @DeliveryStatus");
                    p.Add("@DeliveryStatus", request.DeliveryStatus.Trim());
                }
                if (!string.IsNullOrWhiteSpace(request.MaterialCode))
                {
                    where.Add("m.MaterCode LIKE @MaterialCode");
                    p.Add("@MaterialCode", $"%{request.MaterialCode.Trim()}%");
                }
                if (!string.IsNullOrWhiteSpace(request.MaterialName))
                {
                    where.Add("m.MaterName LIKE @MaterialName");
                    p.Add("@MaterialName", $"%{request.MaterialName.Trim()}%");
                }
                if (request.StartDeliveryDate.HasValue)
                {
                    where.Add("so.DeliveryDate >= @StartDeliveryDate");
                    p.Add("@StartDeliveryDate", request.StartDeliveryDate.Value.Date);
                }
                if (request.EndDeliveryDate.HasValue)
                {
                    where.Add("so.DeliveryDate <= @EndDeliveryDate");
                    p.Add("@EndDeliveryDate", request.EndDeliveryDate.Value.Date.AddDays(1).AddMilliseconds(-1));
                }

                var whereClause = where.Count > 0 ? "WHERE " + string.Join(" AND ", where) : string.Empty;

                // count（按明细行计数）
                var countSql = $@"
                    SELECT COUNT(*)
                    FROM SalesOrder so
                    INNER JOIN SalesOrderDetail sod ON so.Id = sod.SalesOrderId AND sod.IsDeleted = 0
                    INNER JOIN Mater m ON sod.MaterId = m.Id AND m.IsDeleted = 0
                    WHERE so.IsDeleted = 0
                    {{extraWhere}}".Replace("{extraWhere}",
                        string.IsNullOrEmpty(whereClause) ? string.Empty : " AND " + string.Join(" AND ", where));

                var total = await connection.ExecuteScalarAsync<int>(countSql, p);

                // page
                var offset = (request.PageIndex - 1) * request.PageSize;
                var dataSql = $@"
                    SELECT
                        (@row_number:=@row_number + 1) AS SerialNumber,
                        so.SalesPerson AS SalesPerson,
                        so.OrderCode AS SalesOrderNumber,
                        so.BusinessType AS BusinessType,
                        so.DeliveryDate AS DeliveryDate,
                        so.DeliveryStatus AS DeliveryStatus,
                        m.MaterCode AS MaterialCode,
                        m.MaterName AS MaterialName,
                        IFNULL(sod.Remark, '') AS QualityRequirement,
                        IFNULL(m.SpecDesc, '') AS PackagingRequirement,
                        IFNULL(so.Remark, '') AS Remark,
                        sod.Num AS OrderQuantity
                    FROM SalesOrder so
                    INNER JOIN SalesOrderDetail sod ON so.Id = sod.SalesOrderId AND sod.IsDeleted = 0
                    INNER JOIN Mater m ON sod.MaterId = m.Id AND m.IsDeleted = 0
                    CROSS JOIN (SELECT @row_number := {offset}) r
                    WHERE so.IsDeleted = 0
                    {{extraWhere}}
                    ORDER BY so.OrderDate DESC, so.Id DESC
                    LIMIT @PageSize OFFSET @Offset".Replace("{extraWhere}",
                        where.Count == 0 ? string.Empty : " AND " + string.Join(" AND ", where));

                p.Add("@Offset", offset);
                p.Add("@PageSize", request.PageSize);

                var rows = await connection.QueryAsync<SalesOrderPlanItemDto>(dataSql, p);

                return new APIResult<GetSalesOrderPlanListDto>
                {
                    Code = Success,
                    Msg = "查询成功",
                    Data = new GetSalesOrderPlanListDto
                    {
                        TotalCount = total,
                        Items = rows.ToList()
                    }
                };
            }
            catch (Exception ex)
            {
                return new APIResult<GetSalesOrderPlanListDto>
                {
                    Code = Error,
                    Msg = $"查询失败: {ex.Message}",
                    Data = default
                };
            }
        }
    }
}
